from flask import Flask, request, jsonify
import mysql.connector
from mysql.connector import Error
from flask_cors import CORS
app = Flask(__name__)
CORS(app)

# 数据库连接函数
def get_db_connection():
    return mysql.connector.connect(
        host="120.46.13.247",
        user="root",
        password="1qaz@WSX#EDC",
        database="linux_exam"
    )


# 显示所有图书
@app.route('/list_book', methods=['GET'])
def list_book():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute('SELECT * FROM t_book')
    books = cursor.fetchall()
    cursor.close()
    conn.close()
    return jsonify(books)


# 添加图书

@app.route('/add_book', methods=['GET'])
def add_book():
    # 从 URL 获取图书信息
    number = request.args.get('number')
    name = request.args.get('name')
    author = request.args.get('author')

    conn = get_db_connection()
    cursor = conn.cursor()

    # 先查询数据库中是否已存在该编号的图书
    cursor.execute('SELECT * FROM t_book WHERE number = %s', (number,))
    existing_book = cursor.fetchone()
    if existing_book:
        cursor.close()
        conn.close()
        return jsonify({"message": "图书编号已存在，添加失败！"}), 400

    try:
        # 如果编号不存在，则插入图书信息到数据库
        cursor.execute('INSERT INTO t_book (number, name, author) VALUES (%s, %s, %s)',
                       (number, name, author))
        conn.commit()
    except Exception as e:
        print(f"插入图书信息时出现错误: {e}")
        cursor.close()
        conn.close()
        return jsonify({"message": "添加图书信息出现内部错误，请稍后再试！"}), 500

    cursor.close()
    conn.close()

    # 返回添加成功的消息
    return jsonify({"message": "图书信息添加成功!"}), 200

#更新图书信息

@app.route('/update_book', methods=['GET'])
def update_book():
    # 从URL获取图书相关参数，这里假设传入图书编号（id）、书名（name）、作者（author）来更新图书信息，你可按需调整
    book_id = request.args.get('number')
    new_name = request.args.get('name')
    new_author = request.args.get('author')

    # 参数验证
    if not book_id:
        return jsonify({"message": "图书编号（number）不能为空"}), 400
    try:
        book_id = int(book_id)
    except ValueError:
        return jsonify({"message": "图书编号（number）应为整数类型"}), 400

    if not new_name:
        return jsonify({"message": "书名不能为空"}), 400
    if not new_author:
        return jsonify({"message": "作者不能为空"}), 400

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # 开启事务
        conn.start_transaction()

        # 构建UPDATE语句，只更新传入了新值的字段，避免不必要的更新
        update_fields = []
        update_values = []
        if new_name:
            update_fields.append("name = %s")
            update_values.append(new_name)
        if new_author:
            update_fields.append("author = %s")
            update_values.append(new_author)

        update_query = "UPDATE t_book SET " + ", ".join(update_fields) + " WHERE number = %s"
        update_values.append(book_id)

        cursor.execute(update_query, tuple(update_values))
        # 提交事务
        conn.commit()

        # 查询更新后的图书信息并返回给客户端，让客户端能看到更新后的具体内容（可选步骤，可根据需求决定是否返回详细信息）
        cursor.execute("SELECT * FROM t_book WHERE number = %s", (book_id,))
        updated_book = cursor.fetchone()
        return jsonify({"message": "图书信息更新成功"})

    except Exception as e:
        print(f"数据库更新操作出现错误: {e}")
        # 出现异常回滚事务
        conn.rollback()
        return jsonify({"message": "图书信息更新失败，请稍后再试"}), 500
    return jsonify({"message":"图书信息更改成功"})

# 删除图书
@app.route('/delete_book', methods=['GET'])
def delete_book():
    number = request.args.get('number')
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM t_book WHERE number = %s', (number,))
    conn.commit()
    cursor.close()
    conn.close()

    # 返回删除成功的消息
    return jsonify({"message":"删除成功!"})


# 获取图书详细信息
@app.route('/get_book', methods=['GET'])
def get_book():
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        number = request.args.get('number')

        with conn.cursor(dictionary=True) as cursor:
            cursor.execute('SELECT * FROM t_book WHERE number = %s', (number,))
            books = cursor.fetchall()  # 确保所有结果都被读取

            if books:
                book = books[0]  # 假设 number 是唯一键，所以只返回第一个结果
                return jsonify(book)
            else:
                return jsonify({"message": "图书信息未找到!"}), 404

    except Exception as e:
        app.logger.error(f"Error fetching book: {e}")
        return jsonify({"message": "服务器错误"}), 500
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


if __name__ == '__main__':
    app.run(debug=True)
